# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：stock_fund_flow.py
#日期：2019-05-15
#备注：获取东方财富网的每天统计的资金流历史数据存入到数据库，fundFlow_to_mysql()  
      http://data.eastmoney.com/zjlx/dpzjlx.html       
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import requests
import time
import re
from datetime import datetime
import threading

from pacong.stock.stock_config import db,cursor,headers
from pycacho.cachobase.logger import Logger

logger = Logger("stockFundFlow").get_log()

date = datetime.now().strftime('%Y-%m-%d')
lock = threading.Lock()  # 创建锁

def createTable():
    sql1 = 'drop table if exists stock_fundFlow_all;'
    sql2 = 'create table stock_fundFlow_all(flow_date varchar(20),flow_shPrice float,flow_shChange float,flow_szPrice float,flow_szChange float,' \
           'flow_total float,flow_total_rate float,flow_huge float,flow_huge_rate float,flow_big float,flow_big_rate float,flow_middle float,flow_middle_rate float,flow_small float,flow_small_rate float,' \
           'PRIMARY KEY (flow_date));'
    sql3 = 'create table stock_fundFlow_single(fs_date varchar(30),fs_stockNo varchar(30),fs_stockName varchar(30),fs_price float,fs_priceChg float,fs_income float,fs_incomeRate float,' \
           'fs_huge float,fs_hugeRate float,fs_big float,fs_bigRate float,fs_middle float,fs_middleRate float,fs_small float,fs_smallRate float,fs_time varchar(30),' \
           'PRIMARY KEY (fs_date,fs_stockNo));'
    sql4 = 'create table stock_fundFlow_theme(ft_date varchar(30),ft_type varchar(30),ft_typeNo varchar(30),ft_typeName varchar(30),ft_priceChg float,ft_income float,ft_incomeRate float,' \
           'ft_huge float,ft_hugeRate float,ft_big float,ft_bigRate float,ft_middle float,ft_middleRate float,ft_small float,ft_smallRate float,ft_maxStockNo varchar(30),ft_maxStockName varchar(30),' \
           'PRIMARY KEY (ft_date,ft_typeNo));'

    cursor.execute(sql1)
    cursor.execute(sql2)
    #cursor.execute(sql4)

    db.commit()


def get_fundFlow_all():
    url = 'http://data.eastmoney.com/zjlx/dpzjlx.html'
    resp = requests.get(url, headers=headers)
    req = resp.text
    data = re.findall(r'var DefaultJson=\[(.*)\];', req)[0].split('\",\"')
    #print(data)
    for flows in data:
        flow = flows.replace('\"','').split(',')
        #print(flow)
        flow_date = flow[0]                                   # 统计日期
        flow_shPrice = float(flow[11])                        # 上证收盘价
        flow_shChange = float(flow[12].replace('%',''))       # 上证涨跌幅(%)
        flow_szPrice = float(flow[13])                        # 深证收盘价
        flow_szChange = float(flow[14].replace('%',''))       # 深证涨跌幅(%)
        flow_total = float(flow[1])                           # 主力净流入(亿)
        flow_total_rate = float(flow[2].replace('%', ''))     # 主力净流入占比(%)
        flow_huge = float(flow[3])                            # 超大单净流入净流入(亿)
        flow_huge_rate = float(flow[4].replace('%', ''))      # 超大单净流入净流入占比(%)
        flow_big = float(flow[5])                             # 大单净流入净流入(亿)
        flow_big_rate = float(flow[6].replace('%', ''))       # 大单净流入净流入占比(%)
        flow_middle = float(flow[7])                          # 中单净流入净流入(亿)
        flow_middle_rate = float(flow[8].replace('%', ''))    # 中单净流入净流入占比(%)
        flow_small = float(flow[9])                          # 中单净流入净流入(亿)
        flow_small_rate = float(flow[10].replace('%', ''))    # 中单净流入净流入占比(%)
        fundFlow_to_mysql(flow_date,flow_shPrice,flow_shChange,flow_szPrice,flow_szChange,flow_total,flow_total_rate,
              flow_huge,flow_huge_rate,flow_big,flow_big_rate,flow_middle,flow_middle_rate,flow_small,flow_small_rate)


# 将大盘资金流向历史数据(沪深两市)存入到数据库
def fundFlow_to_mysql(flow_date,flow_shPrice,flow_shChange,flow_szPrice,flow_szChange,flow_total,flow_total_rate,
              flow_huge,flow_huge_rate,flow_big,flow_big_rate,flow_middle,flow_middle_rate,flow_small,flow_small_rate):
    sql = "insert into stock_fundFlow_all (flow_date,flow_shPrice,flow_shChange,flow_szPrice,flow_szChange,flow_total,flow_total_rate,flow_huge,flow_huge_rate,flow_big,flow_big_rate,flow_middle,flow_middle_rate,flow_small,flow_small_rate) " \
          "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
          % (flow_date,flow_shPrice,flow_shChange,flow_szPrice,flow_szChange,flow_total,flow_total_rate,flow_huge,flow_huge_rate,flow_big,flow_big_rate,flow_middle,flow_middle_rate,flow_small,flow_small_rate)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        logger.info('%s的资金流数据(沪深两市)插入成功！',flow_date)


# 获取股票交易信息
def fundFlow_stock(p):
    url = 'http://nufm.dfcfw.com/EM_Finance2014NumericApplication/JS.aspx?type=ct&st=(BalFlowMain)&sr=-1&p={0}&ps=50&token=894050c76af8597a853f5b408b759f5d&cmd=C._AB&sty=DCFFITA'.format(p)
    resp = requests.get(url, headers=headers)
    data = resp.text
    data_list = eval(data)
    logger.info('%s的资金流链接！', url)
    #print(type(data_list),data_list)
    for flows in data_list:
        flow = flows.split(',')
        #print(flow)
        if flow[5]!= '-':
            fs_date = date
            fs_stockNo = flow[1]
            fs_stockName = flow[2]
            fs_price = float(flow[3])                   # 收盘价
            fs_priceChg = float(flow[4])                # 今日涨跌幅
            fs_income = float(flow[5])                  # 主力净流入额(万为单位)
            fs_incomeRate = float(flow[6])              # 主力净流入额占比(%)
            fs_huge = float(flow[7])                    # 主力超大单流入额(万为单位)
            fs_hugeRate = float(flow[8])                # 主力大单流入额占比(%)
            fs_big = float(flow[9])                     # 主力大单流入额(万为单位)
            fs_bigRate = float(flow[10])                # 主力大单流入额占比(%)
            fs_middle = float(flow[11])                 # 主力中单流入额(万为单位)
            fs_middleRate = float(flow[12])             # 主力中单流入额占比(%)
            fs_small = float(flow[13])                  # 主力小单流入额(万为单位)
            fs_smallRate = float(flow[14])              # 主力小单流入额占比(%)
            fs_time = flow[15]                          # 获取的时间
            with lock:
                fsFlow_to_mysql(fs_date,fs_stockNo,fs_stockName,fs_price,fs_priceChg,fs_income,fs_incomeRate,fs_huge,fs_hugeRate,fs_big,fs_bigRate,fs_middle,fs_middleRate,fs_small,fs_smallRate,fs_time)


# 将大盘资金流向历史数据(沪深两市)存入到数据库
def fsFlow_to_mysql(fs_date,fs_stockNo,fs_stockName,fs_price,fs_priceChg,fs_income,fs_incomeRate,
              fs_huge,fs_hugeRate,fs_big,fs_bigRate,fs_middle,fs_middleRate,fs_small,fs_smallRate,fs_time):
    sql = "insert into stock_fundFlow_single (fs_date,fs_stockNo,fs_stockName,fs_price,fs_priceChg,fs_income,fs_incomeRate,fs_huge,fs_hugeRate,fs_big,fs_bigRate,fs_middle,fs_middleRate,fs_small,fs_smallRate,fs_time) " \
          "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
          % (fs_date,fs_stockNo,fs_stockName,fs_price,fs_priceChg,fs_income,fs_incomeRate,
              fs_huge,fs_hugeRate,fs_big,fs_bigRate,fs_middle,fs_middleRate,fs_small,fs_smallRate,fs_time)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        logger.info('%s的资金流数据插入成功！',fs_stockNo)


# 获取行业、概念、地域交易信息
def fundFlow_theme(cmd,p,type):
    url = 'http://nufm.dfcfw.com/EM_Finance2014NumericApplication/JS.aspx?cmd={0}&type=ct&st=(BalFlowMain)&sr=-1&p={1}&ps=50&token=894050c76af8597a853f5b408b759f5d&sty=DCFFITABK'.format(cmd,p)
    resp = requests.get(url, headers=headers)
    data = resp.text
    data_list = eval(data)
    logger.info('%s的资金流链接！', url)
    #print(type(data_list),data_list)
    for flows in data_list:
        flow = flows.split(',')
        #print(flow)
        if flow[5]!= '-':
            ft_date = date
            ft_type = type
            ft_typeNo = flow[1]
            ft_typeName = flow[2]
            ft_priceChg = float(flow[3])                # 今日涨跌幅
            ft_income = float(flow[4])                  # 主力净流入额(万为单位)
            ft_incomeRate = float(flow[5])              # 主力净流入额占比(%)
            ft_huge = float(flow[6])                    # 主力超大单流入额(万为单位)
            ft_hugeRate = float(flow[7])                # 主力大单流入额占比(%)
            ft_big = float(flow[8])                     # 主力大单流入额(万为单位)
            ft_bigRate = float(flow[9])                 # 主力大单流入额占比(%)
            ft_middle = float(flow[10])                 # 主力中单流入额(万为单位)
            ft_middleRate = float(flow[11])             # 主力中单流入额占比(%)
            ft_small = float(flow[12])                  # 主力小单流入额(万为单位)
            ft_smallRate = float(flow[13])              # 主力小单流入额占比(%)
            ft_maxStockNo = flow[15]                    # 今日主力净流入最大股代码
            ft_maxStockName = flow[14]                  # 今日主力净流入最大股名称
            ftFlow_to_mysql(ft_date,ft_type,ft_typeNo,ft_typeName,ft_priceChg,ft_income,ft_incomeRate,ft_huge,ft_hugeRate,ft_big,ft_bigRate,ft_middle,ft_middleRate,ft_small,ft_smallRate,ft_maxStockNo,ft_maxStockName)


# 将板块资金流向存入到数据库
def ftFlow_to_mysql(ft_date,ft_type,ft_typeNo,ft_typeName,ft_priceChg,ft_income,ft_incomeRate,
              ft_huge,ft_hugeRate,ft_big,ft_bigRate,ft_middle,ft_middleRate,ft_small,ft_smallRate,ft_maxStockNo,ft_maxStockName):
    sql = "insert into stock_fundFlow_theme (ft_date,ft_type,ft_typeNo,ft_typeName,ft_priceChg,ft_income,ft_incomeRate," \
          "ft_huge,ft_hugeRate,ft_big,ft_bigRate,ft_middle,ft_middleRate,ft_small,ft_smallRate,ft_maxStockNo,ft_maxStockName) " \
          "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
          % (ft_date,ft_type,ft_typeNo,ft_typeName,ft_priceChg,ft_income,ft_incomeRate,
              ft_huge,ft_hugeRate,ft_big,ft_bigRate,ft_middle,ft_middleRate,ft_small,ft_smallRate,ft_maxStockNo,ft_maxStockName)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        logger.info('%s的资金流数据插入成功！',ft_typeName)


def main():
    cTime = time.time()

    get_fundFlow_all()

    # 板块资金流
    for i in range(1,3):
        fundFlow_theme('C._BKHY',i,'行业板块')

    for j in range(1,6):
        fundFlow_theme('C._BKGN', j, '概念板块')

    fundFlow_theme('C._BKDY', 1, '地域板块')

    # 个股资金流
    threads = [threading.Thread(target=fundFlow_stock, args=(t,)) for t in range(1, 76)]  # 第1~75页
    for t in threads:
        t.start()

    for t in threads:
        t.join()

    logger.info("总共耗时：%s ", time.time() - cTime)

if __name__ == '__main__':
    createTable()
    main()
